﻿using System;
using System.Data;
using System.Text;
using System.Data.OleDb;
using ihongma.DBUtility;
using ihongma.Common;
using System.Collections.Generic;
using System.Collections;//Please add references
namespace ihongma.DAL
{
	/// <summary>
	/// 数据访问类:webcase
	/// </summary>
	public partial class webcase
	{
        tag tagmodel = new tag();
        web_photo web_photo = new web_photo();

		public webcase()
		{}
		#region  Method
        /// <summary>
        /// 得到最大ID
        /// </summary>
        private int GetMaxId(OleDbConnection conn, OleDbTransaction trans)
        {
            string strSql = "select top 1 id from dt_webcase order by id desc";
            object obj = DbHelperOleDb.GetSingle(conn, trans, strSql);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }
		/// <summary>
		/// 得到最大ID
		/// </summary>
		public int GetMaxId()
		{
		return DbHelperOleDb.GetMaxID("id", "dt_webcase"); 
		}

		/// <summary>
		/// 是否存在该记录
		/// </summary>
		public bool Exists(int id)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) from dt_webcase");
			strSql.Append(" where id=@id");
			OleDbParameter[] parameters = {
					new OleDbParameter("@id", OleDbType.Integer,4)
};
			parameters[0].Value = id;

			return DbHelperOleDb.Exists(strSql.ToString(),parameters);
		}


		/// <summary>
		/// 增加一条数据
		/// </summary>
        public int Add(ihongma.Model.webcase model)
        {

            int newId;
            int newId2;
            using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString))
            {
                conn.Open();
                using (OleDbTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        StringBuilder strSql = new StringBuilder();
                        strSql.Append("insert into dt_webcase(");
                        strSql.Append("[name],[category],[describe],[tags],[weburl],[color],[state],[dianping],[img],[addtime],[click],[is_top])");
                        strSql.Append(" values (");
                        strSql.Append("@name,@category,@describe,@tags,@weburl,@color,@state,@dianping,@img,@addtime,@click,@is_top)");
                        OleDbParameter[] parameters = {
					new OleDbParameter("@name", OleDbType.VarChar,255),
					new OleDbParameter("@category", OleDbType.Integer,4),
					new OleDbParameter("@describe", OleDbType.VarChar,255),
					new OleDbParameter("@tags", OleDbType.VarChar,255),
					new OleDbParameter("@weburl", OleDbType.VarChar,255),
					new OleDbParameter("@color", OleDbType.VarChar,255),
					new OleDbParameter("@state", OleDbType.Integer,4),
					new OleDbParameter("@dianping", OleDbType.VarChar,255),
					new OleDbParameter("@img", OleDbType.VarChar,255),
					new OleDbParameter("@addtime", OleDbType.VarChar,255),
					new OleDbParameter("@click", OleDbType.Integer,4),
					new OleDbParameter("@is_top", OleDbType.Integer,4)};
                        parameters[0].Value = model.name;
                        parameters[1].Value = model.category;
                        parameters[2].Value = model.describe;
                        parameters[3].Value = model.tags;
                        parameters[4].Value = model.weburl;
                        parameters[5].Value = model.color;
                        parameters[6].Value = model.state;
                        parameters[7].Value = model.dianping;
                        parameters[8].Value = model.img;
                        parameters[9].Value = model.addtime;
                        parameters[10].Value = model.click;
                        parameters[11].Value = model.is_top;

                        DbHelperOleDb.ExecuteSql(conn, trans, strSql.ToString(), parameters); ;

                        newId = GetMaxId(conn, trans);

                        if (model.Photolist != null)
                        {
                            StringBuilder strSql2;
                            foreach (Model.web_photo models in model.Photolist)
                            {

                                strSql2 = new StringBuilder();
                                strSql2.Append("insert into dt_web_photo(");
                                strSql2.Append("photo_id,big_img,small_img)");
                                strSql2.Append(" values (");
                                strSql2.Append("@photo_id,@big_img,@small_img)");
                                OleDbParameter[] parameters2 = {
					new OleDbParameter("@photo_id", OleDbType.Integer,4),
					new OleDbParameter("@big_img", OleDbType.VarChar,255),
					new OleDbParameter("@small_img", OleDbType.VarChar,255)};
                                parameters2[0].Value = newId;
                                parameters2[1].Value = models.big_img;
                                parameters2[2].Value = models.small_img;

                                DbHelperOleDb.ExecuteSql(conn, trans, strSql2.ToString(), parameters2);
                                newId2 = new web_photo().GetMaxId(conn, trans);
                                if (models.Taglist != null)
                                {
                                    StringBuilder strSql3;
                                    foreach (Model.tag modelss in models.Taglist)
                                    {
                                        strSql3 = new StringBuilder();
                                        strSql3.Append("insert into dt_tag(");
                                        strSql3.Append("x,y,width,height,message,photoid)");
                                        strSql3.Append(" values (");
                                        strSql3.Append("@x,@y,@width,@height,@message,@photoid)");
                                        OleDbParameter[] parameters3 = {
					new OleDbParameter("@x", OleDbType.Integer,4),
					new OleDbParameter("@y", OleDbType.Integer,4),
					new OleDbParameter("@width", OleDbType.Integer,4),
					new OleDbParameter("@height", OleDbType.Integer,4),
					new OleDbParameter("@message", OleDbType.VarChar,255),
					new OleDbParameter("@photoid", OleDbType.Integer,4)};
                                        parameters3[0].Value = modelss.x;
                                        parameters3[1].Value = modelss.y;
                                        parameters3[2].Value = modelss.width;
                                        parameters3[3].Value = modelss.height;
                                        parameters3[4].Value = modelss.message;
                                        parameters3[5].Value = newId2;
                                        DbHelperOleDb.ExecuteSql(conn, trans, strSql3.ToString(), parameters3);
                                    }
                                }

                            }
                        }

                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        return -1;
                    }
                }
            }
            return newId;
        }


        /// <summary>
        /// 修改一列数据
        /// </summary>
        public void UpdateField(int id, string strValue)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update dt_webcase set " + strValue);
            strSql.Append(" where id=" + id);
            DbHelperOleDb.ExecuteSql(strSql.ToString());
        }
         /// <summary>
        /// 查找不存在的图片并删除已删除的图片及数据
        /// </summary>
        private void DeleteAlbumList(OleDbConnection conn, OleDbTransaction trans, List<Model.web_photo> models, int photo_id)
        {
            StringBuilder idList = new StringBuilder();
            if (models != null)
            {
                foreach (Model.web_photo modelt in models)
                {
                   
                    if (modelt.id > 0)
                    {
                        idList.Append(modelt.id + ",");
                    }
                }
            }
            string id_list = Utils.DelLastChar(idList.ToString(), ",");
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,big_img,small_img from dt_web_photo where photo_id=" + photo_id);
            if (!string.IsNullOrEmpty(id_list))
            {
                strSql.Append(" and id not in(" + id_list + ")");
            }
            DataSet ds = DbHelperOleDb.Query(conn, trans, strSql.ToString());
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                int rows = DbHelperOleDb.ExecuteSql(conn, trans, "delete from dt_web_photo where id=" + dr["id"].ToString()); //删除数据库
                if (rows > 0)
                {
                    Utils.DeleteFile(dr["big_img"].ToString()); //删除原图
                    Utils.DeleteFile(dr["small_img"].ToString()); //删除缩略图
                }
            }
        }

           /// <summary>
        /// 查找不存在的图片并删除已删除的图片及数据
        /// </summary>
        private void Deletetges(OleDbConnection conn, OleDbTransaction trans, List<Model.tag> models, int photo_id)
        {
            StringBuilder idList = new StringBuilder();
            if (models != null)
            {
                foreach (Model.tag modelt in models)
                {
                   
                    if (modelt.id > 0)
                    {
                        idList.Append(modelt.id + ",");
                    }
                }
            }
            string id_list = Utils.DelLastChar(idList.ToString(), ",");
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from dt_tag where photoid=" + photo_id);
            if (!string.IsNullOrEmpty(id_list))
            {
                strSql.Append(" and id not in(" + id_list + ")");
            }
            DataSet ds = DbHelperOleDb.Query(conn, trans, strSql.ToString());
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                int rows = DbHelperOleDb.ExecuteSql(conn, trans, "delete from dt_tag where id=" + dr["id"].ToString()); //删除数据库
              
            }
        }
		/// <summary>
		/// 更新一条数据
		/// </summary>
        public bool Update(ihongma.Model.webcase model)
        {
            using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString))
            {
                conn.Open();
                using (OleDbTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        StringBuilder strSql = new StringBuilder();
                        strSql.Append("update dt_webcase set ");
                        strSql.Append("[name]=@name,");
                        strSql.Append("[category]=@category,");
                        strSql.Append("[describe]=@describe,");
                        strSql.Append("[tags]=@tags,");
                        strSql.Append("[weburl]=@weburl,");
                        strSql.Append("[color]=@color,");
                        strSql.Append("[state]=@state,");
                        strSql.Append("[dianping]=@dianping,");
                        strSql.Append("[img]=@img,");
                        strSql.Append("[addtime]=@addtime,");
                        strSql.Append("[click]=@click,");
                        strSql.Append("[is_top]=@is_top");
                        strSql.Append(" where id=@id");
                        OleDbParameter[] parameters = {
					new OleDbParameter("@name", OleDbType.VarChar,255),
					new OleDbParameter("@category", OleDbType.Integer,4),
					new OleDbParameter("@describe", OleDbType.VarChar,255),
					new OleDbParameter("@tags", OleDbType.VarChar,255),
					new OleDbParameter("@weburl", OleDbType.VarChar,255),
					new OleDbParameter("@color", OleDbType.VarChar,255),
					new OleDbParameter("@state", OleDbType.Integer,4),
					new OleDbParameter("@dianping", OleDbType.VarChar,255),
					new OleDbParameter("@img", OleDbType.VarChar,255),
					new OleDbParameter("@addtime", OleDbType.VarChar,255),
					new OleDbParameter("@click", OleDbType.Integer,4),
					new OleDbParameter("@is_top", OleDbType.Integer,4),
					new OleDbParameter("@id", OleDbType.Integer,4)};
                        parameters[0].Value = model.name;
                        parameters[1].Value = model.category;
                        parameters[2].Value = model.describe;
                        parameters[3].Value = model.tags;
                        parameters[4].Value = model.weburl;
                        parameters[5].Value = model.color;
                        parameters[6].Value = model.state;
                        parameters[7].Value = model.dianping;
                        parameters[8].Value = model.img;
                        parameters[9].Value = model.addtime;
                        parameters[10].Value = model.click;
                        parameters[11].Value = model.is_top;
                        parameters[12].Value = model.id;


                        DbHelperOleDb.ExecuteSql(conn, trans, strSql.ToString(), parameters);
                        //删除已删除的图片
                        DeleteAlbumList(conn, trans, model.Photolist, model.id);
                        StringBuilder strSql2;
                        if (model.Photolist != null)
                        {
                            foreach (Model.web_photo models in model.Photolist)
                            {
                                if (models.id > 0)
                                {

                                    strSql2 = new StringBuilder();
                                    strSql2.Append("update dt_web_photo set ");
                                    strSql2.Append("photo_id=@photo_id,");
                                    strSql2.Append("big_img=@big_img,");
                                    strSql2.Append("small_img=@small_img");
                                    strSql2.Append(" where id=@id");
                                    OleDbParameter[] parameters2 = {
					new OleDbParameter("@photo_id", OleDbType.Integer,4),
					new OleDbParameter("@big_img", OleDbType.VarChar,255),
					new OleDbParameter("@small_img", OleDbType.VarChar,255),
					new OleDbParameter("@id", OleDbType.Integer,4)};
                                    parameters2[0].Value = models.photo_id;
                                    parameters2[1].Value = models.big_img;
                                    parameters2[2].Value = models.small_img;
                                    parameters2[3].Value = models.id;
                                    DbHelperOleDb.ExecuteSql(conn, trans, strSql2.ToString(), parameters2);
                                    Deletetges(conn, trans, models.Taglist, models.id);
                                    if (models.Taglist != null)
                                    {
                                        DbHelperAge tagage = new DbHelperAge();
                                        foreach (Model.tag modelss in models.Taglist)
                                        {
                                            if (modelss.id > 0)
                                            {
                                                tagage = new tag().Update(modelss);
                                                DbHelperOleDb.ExecuteSql(conn, trans, tagage.StrSql, tagage.OleDbParameterList);
                                            }
                                            else
                                            {
                                                //这一步有问题
                                                tagage = new tag().Add(modelss);
                                                DbHelperOleDb.ExecuteSql(conn, trans, tagage.StrSql, tagage.OleDbParameterList);
                                            }
                                        }
                                    }
                                }
                                else
                                {
                                    strSql2 = new StringBuilder();
                                    strSql2.Append("insert into dt_web_photo(");
                                    strSql2.Append("photo_id,big_img,small_img)");
                                    strSql2.Append(" values (");
                                    strSql2.Append("@photo_id,@big_img,@small_img)");
                                    OleDbParameter[] parameters2 = {
					new OleDbParameter("@photo_id", OleDbType.Integer,4),
					new OleDbParameter("@big_img", OleDbType.VarChar,255),
					new OleDbParameter("@small_img", OleDbType.VarChar,255)};
                                    parameters2[0].Value = models.photo_id;
                                    parameters2[1].Value = models.big_img;
                                    parameters2[2].Value = models.small_img;
                                    DbHelperOleDb.ExecuteSql(conn, trans, strSql2.ToString(), parameters2);
                                }
                            }
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        return false;
                    }
                }
            }
            return true;
            //if (model.Photolist!=null)
            //{
            //    return true;
            //}
            //else
            //{
            //    return false;
            //}
        }

		/// <summary>
		/// 删除一条数据
		/// </summary>
		public bool Delete(int id)
		{
            Hashtable sqllist = new Hashtable();

            //DbHelperAge age = new DbHelperAge();
            List<Model.web_photo> ls = new List<Model.web_photo>();
            Model.webcase model = GetModel(id);
            if (model != null)
            {
                ls = model.Photolist;
            }
			
           web_photo dalphoto=new web_photo();
           DbHelperAge photo = dalphoto.Delete(id);
          sqllist.Add(photo.StrSql, photo.OleDbParameterList);
           // List<Model.tag> taglist=dalphoto.
          StringBuilder strSql = new StringBuilder();
          strSql.Append("delete from dt_webcase ");
          strSql.Append(" where id=@photo_id");
          OleDbParameter[] parameters = {
					new OleDbParameter("@photo_id", OleDbType.Integer,4)
};
          parameters[0].Value = id;
          sqllist.Add(strSql, parameters);
          string delstr = string.Empty;
            foreach (Model.web_photo t in ls)
            {
                delstr =delstr+t.id.ToString()+","; 
            }
            DbHelperAge tagmodel = new tag().DeleteList(Utils.DelLastComma(delstr));

            sqllist.Add(tagmodel.StrSql, tagmodel.OleDbParameterList);
              bool result = DbHelperOleDb.ExecuteSqlTran(sqllist);
            if (result)
            {
                DeletephotoFile(ls);
            }
            return result;
		
		}
		/// <summary>
		/// 批量删除数据
		/// </summary>
		public bool DeleteList(string idlist )
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from dt_webcase ");
			strSql.Append(" where id in ("+idlist + ")  ");
			int rows=DbHelperOleDb.ExecuteSql(strSql.ToString());
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public ihongma.Model.webcase GetModel(int id)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select [id],[name],[category],[describe],[tags],[weburl],[color],[state],[dianping],[img],[addtime],[click],[is_top] from dt_webcase ");
			strSql.Append(" where id=@id");
			OleDbParameter[] parameters = {
					new OleDbParameter("@id", OleDbType.Integer,4)
};
			parameters[0].Value = id;

			ihongma.Model.webcase model=new ihongma.Model.webcase();
			DataSet ds=DbHelperOleDb.Query(strSql.ToString(),parameters);
			if(ds.Tables[0].Rows.Count>0)
			{
				if(ds.Tables[0].Rows[0]["id"]!=null && ds.Tables[0].Rows[0]["id"].ToString()!="")
				{
					model.id=int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
				}
				if(ds.Tables[0].Rows[0]["name"]!=null && ds.Tables[0].Rows[0]["name"].ToString()!="")
				{
					model.name=ds.Tables[0].Rows[0]["name"].ToString();
				}
				if(ds.Tables[0].Rows[0]["category"]!=null && ds.Tables[0].Rows[0]["category"].ToString()!="")
				{
					model.category=int.Parse(ds.Tables[0].Rows[0]["category"].ToString());
				}
				if(ds.Tables[0].Rows[0]["describe"]!=null && ds.Tables[0].Rows[0]["describe"].ToString()!="")
				{
					model.describe=ds.Tables[0].Rows[0]["describe"].ToString();
				}
				if(ds.Tables[0].Rows[0]["tags"]!=null && ds.Tables[0].Rows[0]["tags"].ToString()!="")
				{
					model.tags=ds.Tables[0].Rows[0]["tags"].ToString();
				}
				if(ds.Tables[0].Rows[0]["weburl"]!=null && ds.Tables[0].Rows[0]["weburl"].ToString()!="")
				{
					model.weburl=ds.Tables[0].Rows[0]["weburl"].ToString();
				}
				if(ds.Tables[0].Rows[0]["color"]!=null && ds.Tables[0].Rows[0]["color"].ToString()!="")
				{
					model.color=ds.Tables[0].Rows[0]["color"].ToString();
				}
				if(ds.Tables[0].Rows[0]["state"]!=null && ds.Tables[0].Rows[0]["state"].ToString()!="")
				{
					model.state=int.Parse(ds.Tables[0].Rows[0]["state"].ToString());
				}
				if(ds.Tables[0].Rows[0]["dianping"]!=null && ds.Tables[0].Rows[0]["dianping"].ToString()!="")
				{
					model.dianping=ds.Tables[0].Rows[0]["dianping"].ToString();
				}
				if(ds.Tables[0].Rows[0]["img"]!=null && ds.Tables[0].Rows[0]["img"].ToString()!="")
				{
					model.img=ds.Tables[0].Rows[0]["img"].ToString();
				}
				if(ds.Tables[0].Rows[0]["addtime"]!=null && ds.Tables[0].Rows[0]["addtime"].ToString()!="")
				{
					model.addtime=ds.Tables[0].Rows[0]["addtime"].ToString();
				}
				if(ds.Tables[0].Rows[0]["click"]!=null && ds.Tables[0].Rows[0]["click"].ToString()!="")
				{
					model.click=int.Parse(ds.Tables[0].Rows[0]["click"].ToString());
				}
				if(ds.Tables[0].Rows[0]["is_top"]!=null && ds.Tables[0].Rows[0]["is_top"].ToString()!="")
				{
					model.is_top=int.Parse(ds.Tables[0].Rows[0]["is_top"].ToString());
				}
                model.Photolist = web_photo.GetList(model.id);
				return model;
			}
			else
			{
				return null;
			}
		}
        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" [id],[name],[category],[describe],[tags],[weburl],[color],[state],[dianping],[img],[addtime],[click],[is_top]");
            strSql.Append(" FROM dt_webcase ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperOleDb.Query(strSql.ToString());
        }
		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select id,name,category,describe,tags,weburl,color,state,dianping,img,addtime,click,is_top ");
			strSql.Append(" FROM dt_webcase ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return DbHelperOleDb.Query(strSql.ToString());
		}
        /// <summary>
        /// 删除相册图片
        /// </summary>
        private void DeletephotoFile(List<Model.web_photo> models)
        {
            if (models != null)
            {
                foreach (Model.web_photo modelt in models)
                {
                    Utils.DeleteFile(modelt.big_img);
                    Utils.DeleteFile(modelt.small_img);
                }
            }
        }
        

        /// <summary>
        /// 获得查询分页数据
        /// </summary>
        public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM dt_webcase");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperOleDb.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
            return DbHelperOleDb.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
        }

		/*
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetList(int PageSize,int PageIndex,string strWhere)
		{
			OleDbParameter[] parameters = {
					new OleDbParameter("@tblName", OleDbType.VarChar, 255),
					new OleDbParameter("@fldName", OleDbType.VarChar, 255),
					new OleDbParameter("@PageSize", OleDbType.Integer),
					new OleDbParameter("@PageIndex", OleDbType.Integer),
					new OleDbParameter("@IsReCount", OleDbType.Boolean),
					new OleDbParameter("@OrderType", OleDbType.Boolean),
					new OleDbParameter("@strWhere", OleDbType.VarChar,1000),
					};
			parameters[0].Value = "dt_webcase";
			parameters[1].Value = "id";
			parameters[2].Value = PageSize;
			parameters[3].Value = PageIndex;
			parameters[4].Value = 0;
			parameters[5].Value = 0;
			parameters[6].Value = strWhere;	
			return DbHelperOleDb.RunProcedure("UP_GetRecordByPage",parameters,"ds");
		}*/

		#endregion  Method
	}
}

